const { pool } = require('../config/database');

/**
 * 数据库操作组件
 * 封装通用的数据库操作方法
 */
class DatabaseComponent {

  /**
   * 执行查询
   * @param {string} sql SQL语句
   * @param {Array} params 参数
   * @returns {Promise<Object>} 查询结果
   */
  static async query(sql, params = []) {
    try {
      const [rows, fields] = await pool.execute(sql, params);
      return {
        success: true,
        data: rows,
        fields: fields,
        affectedRows: rows.affectedRows || 0,
        insertId: rows.insertId || null
      };
    } catch (error) {
      console.error('数据库查询错误:', error);
      return {
        success: false,
        message: '数据库查询失败',
        error: error.message
      };
    }
  }

  /**
   * 执行事务
   * @param {Function} callback 事务回调函数
   * @returns {Promise<Object>} 事务结果
   */
  static async transaction(callback) {
    const connection = await pool.getConnection();
    
    try {
      await connection.beginTransaction();
      
      // 执行事务内容
      const result = await callback(connection);
      
      await connection.commit();
      
      return {
        success: true,
        data: result
      };
    } catch (error) {
      await connection.rollback();
      console.error('事务执行错误:', error);
      return {
        success: false,
        message: '事务执行失败',
        error: error.message
      };
    } finally {
      connection.release();
    }
  }

  /**
   * 检查表是否存在
   * @param {string} tableName 表名
   * @returns {Promise<boolean>} 表是否存在
   */
  static async checkTableExists(tableName) {
    try {
      const sql = `
        SELECT COUNT(*) as count 
        FROM information_schema.tables 
        WHERE table_schema = DATABASE() 
        AND table_name = ?
      `;
      
      const result = await this.query(sql, [tableName]);
      return result.success && result.data[0].count > 0;
    } catch (error) {
      console.error('检查表存在性错误:', error);
      return false;
    }
  }

  /**
   * 获取表结构
   * @param {string} tableName 表名
   * @returns {Promise<Object>} 表结构信息
   */
  static async getTableStructure(tableName) {
    try {
      const sql = 'DESCRIBE ??';
      const result = await this.query(sql, [tableName]);
      
      if (result.success) {
        return {
          success: true,
          data: result.data
        };
      } else {
        return result;
      }
    } catch (error) {
      console.error('获取表结构错误:', error);
      return {
        success: false,
        message: '获取表结构失败',
        error: error.message
      };
    }
  }

  /**
   * 分页查询
   * @param {string} tableName 表名
   * @param {Object} options 查询选项
   * @returns {Promise<Object>} 查询结果
   */
  static async paginate(tableName, options = {}) {
    try {
      const {
        page = 1,
        limit = 10,
        where = '',
        params = [],
        orderBy = 'id DESC',
        select = '*'
      } = options;

      const offset = (page - 1) * limit;
      
      // 构建WHERE子句
      const whereClause = where ? `WHERE ${where}` : '';
      
      // 获取总数
      const countSql = `SELECT COUNT(*) as total FROM ?? ${whereClause}`;
      const countResult = await this.query(countSql, [tableName, ...params]);
      
      if (!countResult.success) {
        return countResult;
      }
      
      const total = countResult.data[0].total;
      
      // 获取数据
      const dataSql = `
        SELECT ${select} 
        FROM ?? 
        ${whereClause} 
        ORDER BY ${orderBy} 
        LIMIT ? OFFSET ?
      `;
      
      const dataResult = await this.query(dataSql, [tableName, ...params, limit, offset]);
      
      if (!dataResult.success) {
        return dataResult;
      }
      
      return {
        success: true,
        data: dataResult.data,
        pagination: {
          current: page,
          pageSize: limit,
          total,
          totalPages: Math.ceil(total / limit)
        }
      };
    } catch (error) {
      console.error('分页查询错误:', error);
      return {
        success: false,
        message: '分页查询失败',
        error: error.message
      };
    }
  }

  /**
   * 插入数据
   * @param {string} tableName 表名
   * @param {Object} data 数据对象
   * @returns {Promise<Object>} 插入结果
   */
  static async insert(tableName, data) {
    try {
      const fields = Object.keys(data);
      const values = Object.values(data);
      const placeholders = fields.map(() => '?').join(',');
      
      const sql = `INSERT INTO ?? (${fields.join(',')}) VALUES (${placeholders})`;
      const result = await this.query(sql, [tableName, ...values]);
      
      return result;
    } catch (error) {
      console.error('插入数据错误:', error);
      return {
        success: false,
        message: '插入数据失败',
        error: error.message
      };
    }
  }

  /**
   * 更新数据
   * @param {string} tableName 表名
   * @param {Object} data 更新数据
   * @param {string} whereClause WHERE条件
   * @param {Array} whereParams WHERE参数
   * @returns {Promise<Object>} 更新结果
   */
  static async update(tableName, data, whereClause, whereParams = []) {
    try {
      const fields = Object.keys(data);
      const values = Object.values(data);
      const setClause = fields.map(field => `${field} = ?`).join(',');
      
      const sql = `UPDATE ?? SET ${setClause} WHERE ${whereClause}`;
      const result = await this.query(sql, [tableName, ...values, ...whereParams]);
      
      return result;
    } catch (error) {
      console.error('更新数据错误:', error);
      return {
        success: false,
        message: '更新数据失败',
        error: error.message
      };
    }
  }

  /**
   * 删除数据
   * @param {string} tableName 表名
   * @param {string} whereClause WHERE条件
   * @param {Array} whereParams WHERE参数
   * @returns {Promise<Object>} 删除结果
   */
  static async delete(tableName, whereClause, whereParams = []) {
    try {
      const sql = `DELETE FROM ?? WHERE ${whereClause}`;
      const result = await this.query(sql, [tableName, ...whereParams]);
      
      return result;
    } catch (error) {
      console.error('删除数据错误:', error);
      return {
        success: false,
        message: '删除数据失败',
        error: error.message
      };
    }
  }

  /**
   * 获取连接池状态
   * @returns {Object} 连接池状态
   */
  static getPoolStatus() {
    return {
      activeConnections: pool._allConnections?.length || 0,
      freeConnections: pool._freeConnections?.length || 0,
      connectionLimit: pool.config.connectionLimit,
      acquiredConnections: pool._acquiringConnections?.length || 0
    };
  }

  /**
   * 关闭连接池
   * @returns {Promise<void>}
   */
  static async closePool() {
    try {
      await pool.end();
      console.log('数据库连接池已关闭');
    } catch (error) {
      console.error('关闭连接池错误:', error);
    }
  }

  /**
   * 检查数据库连接
   * @returns {Promise<Object>} 连接状态
   */
  static async checkConnection() {
    try {
      const result = await this.query('SELECT 1 as connected');
      return {
        success: true,
        message: '数据库连接正常',
        data: result.data
      };
    } catch (error) {
      return {
        success: false,
        message: '数据库连接失败',
        error: error.message
      };
    }
  }
}

module.exports = DatabaseComponent; 